﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Drawing;
using System.Data;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
using Spread.Common.Features.Sample;
using FarPoint.Win.Spread;
using FarPoint.Win.Spread.CellType;
using Spread.Common.DataStore;
using Spread.Common.DataStore.Models;
using FarPoint.Win;
using FarPoint.Win.Spread.Model;
using FarPoint.Win.Spread.Data;
using System.Dynamic;
using System.IO;

namespace GrapeCity.EmployeeAbsenceSchedule
{
    public partial class AbsenceScheduleControl : BaseSampleControl
    {
        #region Members
        DataService _dataService;

        FpSpread fpSpread;
        ComboBoxCellType cmbocellMonth = null;
        ComboBoxCellType cmbocellYear = null;
        UnaryComparisonConditionalFormattingRule sRule;
        UnaryComparisonConditionalFormattingRule vRule;
        UnaryComparisonConditionalFormattingRule pRule;
        UnaryComparisonConditionalFormattingRule c1Rule;
        UnaryComparisonConditionalFormattingRule c2Rule;
        ConditionalFormatting cf;
        DataTable mainTable = new DataTable();
        List<Employee> empList = null;
        List<AbsenceSchedule> absenceSheduleList = null;
        List<string> month = null;
        List<string> year = null;
        int selectedYr = 2018;
        int selectedMonth = 1;
        #endregion

        #region ctor
        public AbsenceScheduleControl()
        {
            InitializeComponent();

            AddUIControls();

            Load += AbsenceSchedule_Load;
        }
        #endregion

        #region Events
        private void AbsenceSchedule_HandleDestroyed(object sender, EventArgs e)
        {
            _dataService.JsonDataSource.SaveObjectToJsonFile(Path.Combine("Data", "AbsenceSchedule.json"), absenceSheduleList);
        }
        private void AbsenceSchedule_Load(object sender, EventArgs e)
        {
            _dataService = new DataService();

            //set rows and columns count
            int colCnt = fpSpread.Sheets[0].Columns.Count = 35;
            int rowCnt = fpSpread.Sheets[0].Rows.Count = 7;

            //set FrozenRowCount 
            fpSpread.Sheets[0].FrozenRowCount = 7;
            //set border            
            fpSpread.ActiveSheet.Rows[0, 4].Border = new LineBorder(Color.Transparent, 1);
            fpSpread.AllowCellOverflow = true;
            fpSpread.AllowEditOverflow = true;
            AddEventHandlers();
            SetCellText();
            SetAlignmentForSpecificCells();
            SetEditingToFalseForCells();

            empList = _dataService.JsonDataSource.GetAllEmployees();
            absenceSheduleList = _dataService.JsonDataSource.GetAbsenceScheduleList();

            SetColumnsRowsVisibility();
            SetRowsHieghtAndColumnWidth(rowCnt, colCnt);
            SetAlignmentforAllRows();
            SetCellSpans();
            SetScrollBars();
            SetColor();
            SetFont();
            AddComboBox(3, 33);
            CreateCondionalRules();
            UpdateDateHeaderAndDateTable(new DateTime(selectedYr, selectedMonth, 1), empList, absenceSheduleList);
            UpdateDataOnSpread();
            SetEditingToFalseForCells();

        }
        private void FpSpread_Change(object sender, ChangeEventArgs e)
        {
            if (e.Row <= fpSpread.Sheets[0].Rows.Count - 2 && e.Row >= 7 && e.Column <= fpSpread.Sheets[0].Columns.Count - 3 && e.Column >= 2)
            {
                fpSpread.Sheets[0].Cells[e.Row, e.Column].Text = fpSpread.Sheets[0].Cells[e.Row, e.Column].Text.Trim().ToUpper();
                SaveChangesBackToList();
            }
        }
        private void FpSpread_ComboSelChange(object sender, EditorNotifyEventArgs e)
        {
            string _ctrlTxt = (e.EditingControl as FpCombo).SelectedItem.ToString();
            selectedMonth = month.IndexOf(_ctrlTxt) >= 0 ? month.IndexOf(_ctrlTxt) + 1 : selectedMonth;
            selectedYr = year.IndexOf(_ctrlTxt) >= 0 ? Convert.ToInt32(year[year.IndexOf(_ctrlTxt)]) : selectedYr;

            UpdateDateHeaderAndDateTable(new DateTime(selectedYr, selectedMonth, 1), empList, absenceSheduleList);
            UpdateDataOnSpread();
        }
        #endregion

        #region PrivateMethods
        private void AddUIControls()
        {
            fpSpread = new FpSpread(LegacyBehaviors.None);
            fpSpread.Sheets.Clear();
            fpSpread.Sheets.Add(new SheetView());
            fpSpread.Dock = DockStyle.Fill;

            pnlMain.Controls.Add(fpSpread);
        }
        private void AddEventHandlers()
        {
            fpSpread.ComboSelChange += FpSpread_ComboSelChange;
            fpSpread.Change += FpSpread_Change;
            HandleDestroyed += AbsenceSchedule_HandleDestroyed;
        }
        private void SetEditingToFalseForCells()
        {
            fpSpread.Sheets[0].Rows[0, 6].Locked = true;
            fpSpread.Sheets[0].Columns[fpSpread.Sheets[0].ColumnCount - 2, fpSpread.Sheets[0].ColumnCount - 1].Locked = true;
            fpSpread.Sheets[0].Cells[4, fpSpread.Sheets[0].ColumnCount - 2, 4, fpSpread.Sheets[0].ColumnCount - 1].Locked = false;   //Cells should be set last  
        }
            private void SetAlignmentForSpecificCells()
        {
            fpSpread.Sheets[0].Cells[2, 3, 2, 3].HorizontalAlignment = CellHorizontalAlignment.Left;
            fpSpread.Sheets[0].Cells[2, 8, 2, 8].HorizontalAlignment = CellHorizontalAlignment.Left;
            fpSpread.Sheets[0].Cells[2, 13, 2, 13].HorizontalAlignment = CellHorizontalAlignment.Left;
            fpSpread.Sheets[0].Cells[2, 18, 2, 18].HorizontalAlignment = CellHorizontalAlignment.Left;
            fpSpread.Sheets[0].Cells[2, 23, 2, 23].HorizontalAlignment = CellHorizontalAlignment.Left;
            fpSpread.Sheets[0].Cells[6, 33, 6, 33].HorizontalAlignment = CellHorizontalAlignment.Left;
            fpSpread.Sheets[0].Cells[2, 3, 2, 3].CellPadding.Left = 5;
            fpSpread.Sheets[0].Cells[2, 8, 2, 8].CellPadding.Left = 5;
            fpSpread.Sheets[0].Cells[2, 13, 2, 13].CellPadding.Left = 5;
            fpSpread.Sheets[0].Cells[2, 18, 2, 18].CellPadding.Left = 5;
            fpSpread.Sheets[0].Cells[2, 23, 2, 23].CellPadding.Left = 5;
            fpSpread.Sheets[0].Cells[6, 33, 6, 33].CellPadding.Left = 5;
        }
        private void SetCellText()
        {
            fpSpread.Sheets[0].Cells[0, 1].Text = "Employee Absence Schedule";
            fpSpread.Sheets[0].Cells[2, 1].Text = "Absence Type Key";
            fpSpread.Sheets[0].Cells[2, 2].Text = "V";
            fpSpread.Sheets[0].Cells[2, 3].Text = "Vacation";
            fpSpread.Sheets[0].Cells[2, 7].Value = "P";
            fpSpread.Sheets[0].Cells[2, 8].Text = "Personal";
            fpSpread.Sheets[0].Cells[2, 12].Value = "S";
            fpSpread.Sheets[0].Cells[2, 13].Text = "Sick";
            fpSpread.Sheets[0].Cells[2, 17].Text = "C1";
            fpSpread.Sheets[0].Cells[2, 18].Text = "Custom 1";
            fpSpread.Sheets[0].Cells[2, 22].Text = "C2";
            fpSpread.Sheets[0].Cells[2, 23].Text = "Custom 2";
            fpSpread.Sheets[0].Cells[4, 1].Text = "Summary";
            fpSpread.Sheets[0].Cells[4, 2].Text = "Dates of Absence";
        }
        private void SetRowsHieghtAndColumnWidth(int rowCont, int colCnt)
        {
            fpSpread.Sheets[0].Rows[0].Height = 70;
            fpSpread.Sheets[0].Rows[4].Height = 40;
            fpSpread.Sheets[0].Columns[1].Width = 150;
            fpSpread.Sheets[0].Columns[2, colCnt - 2].Width = 30;
            fpSpread.Sheets[0].Columns[colCnt - 2, colCnt - 1].Width = 100;
        }
        private void SetCellSpans()
        {
            //Span column, rows and cells           
            fpSpread.Sheets[0].AddSpanCell(0, 1, 2, 34);
            fpSpread.Sheets[0].AddSpanCell(2, 3, 1, 4);
            fpSpread.Sheets[0].AddSpanCell(2, 8, 1, 4);
            fpSpread.Sheets[0].AddSpanCell(2, 13, 1, 4);
            fpSpread.Sheets[0].AddSpanCell(2, 18, 1, 4);
            fpSpread.Sheets[0].AddSpanCell(2, 23, 1, 4);
            fpSpread.Sheets[0].AddSpanCell(4, 2, 1, 31);
            fpSpread.Sheets[0].AddSpanCell(5, 33, 1, 2);
            fpSpread.Sheets[0].AddSpanCell(6, 33, 1, 2);
        }
        private void SetColumnsRowsVisibility()
        {
            //Set visibility of headers and columns
            fpSpread.Sheets[0].ColumnHeader.Visible = false;
            fpSpread.Sheets[0].RowHeader.Visible = false;
            fpSpread.Sheets[0].Columns[0].Visible = false;
        }
        private void SetScrollBars()
        {
            //Set scrollbar
            fpSpread.HorizontalScrollBarPolicy = ScrollBarPolicy.AsNeeded;
            fpSpread.VerticalScrollBarPolicy = ScrollBarPolicy.AsNeeded;
        }
        private void SetColor()
        {
            //set color
            fpSpread.Sheets[0].Rows[2].BackColor = Color.FromArgb(187, 189, 178);
            fpSpread.Sheets[0].Rows[3].BackColor = Color.FromArgb(198, 198, 198);
            fpSpread.Sheets[0].Rows[4].ForeColor = Color.DarkRed;
            fpSpread.Sheets[0].Rows[4].BackColor = Color.FromArgb(187, 189, 178);
            fpSpread.Sheets[0].Columns[1].BackColor = Color.FromArgb(198, 198, 198);
            fpSpread.Sheets[0].Cells[3, 1].BackColor = Color.FromArgb(187, 189, 178);
            fpSpread.Sheets[0].Cells[5, 33, 6, 34].BackColor = Color.FromArgb(187, 189, 178);
            fpSpread.Sheets[0].Cells[5, 1, 6, 1].BackColor = Color.FromArgb(187, 189, 178);
            fpSpread.Sheets[0].Cells[6, 2, 6, 32].ForeColor = Color.White;
            fpSpread.Sheets[0].Cells[6, 2, 6, 32].BackColor = Color.FromArgb(67, 38, 32);
            fpSpread.Sheets[0].Cells[5, 1, 6, 1].Border = new LineBorder(Color.Transparent, 1);

            //Set alternative row color
            fpSpread.Sheets[0].AlternatingRows.Count = 2;
            fpSpread.Sheets[0].AlternatingRows[0].BackColor = Color.WhiteSmoke;
            fpSpread.Sheets[0].AlternatingRows[1].BackColor = Color.Gainsboro;
        }
        private void SetFont()
        {
            //Set font
            fpSpread.Font = new Font("MS Sans Serif", 9, FontStyle.Regular);
            fpSpread.Sheets[0].Rows[0].Font = new Font("MS Sans Serif", 18, FontStyle.Bold);
            fpSpread.Sheets[0].Rows[4].Font = new Font("MS Sans Serif", 10, FontStyle.Bold);
        }
        private void SaveChangesBackToList()
        {
            List<AbsenceSchedule> _AddToList = new List<AbsenceSchedule>();
            foreach (DataRow row in mainTable.Rows)
            {
                int _loc = -1;
                foreach (var item in row.ItemArray)
                {
                    if (_loc > 0 && (item.ToString().ToLower() == "s" || item.ToString().ToLower() == "v" || item.ToString().ToLower() == "p" || item.ToString().ToLower() == "c1" || item.ToString().ToLower() == "c2"))
                    {
                        _AddToList.Add(new AbsenceSchedule()
                        {
                            EmpId = Convert.ToInt32(row[0]),
                            StartDate = new DateTime(selectedYr, selectedMonth, _loc),
                            EndDate = new DateTime(selectedYr, selectedMonth, _loc),
                            Type = item.ToString()
                        });
                    }
                    _loc = _loc + 1;
                }
            }

            //Need to remove
            List<AbsenceSchedule> _removeFromList = new List<AbsenceSchedule>();
            foreach (DataRow row in mainTable.Rows)
            {
                int _loc = -1;
                foreach (var item in row.ItemArray)
                {
                    if (_loc > 0 && (item.ToString().ToLower() != "s" && item.ToString().ToLower() != "v" && item.ToString().ToLower() != "p" && item.ToString().ToLower() != "c1" && item.ToString().ToLower() != "c2"))
                    {
                        _removeFromList.Add(new AbsenceSchedule()
                        {
                            EmpId = Convert.ToInt32(row[0]),
                            StartDate = new DateTime(selectedYr, selectedMonth, _loc),
                            EndDate = new DateTime(selectedYr, selectedMonth, _loc),
                            Type = item.ToString()
                        });
                    }
                    _loc = _loc + 1;
                }
            }

            List<Employee> _filteredList = empList.Where(e => e.DOJ <= new DateTime(selectedYr, selectedMonth, 1) && (e.DOR == null || e.DOR >= new DateTime(selectedYr, selectedMonth, 1))).ToList();
            //Update old list
            foreach (var absence in _filteredList)
            {

                //Add update
                foreach (var item in _AddToList.Where(i => i.EmpId == absence.Id))
                {
                    AbsenceSchedule _alreadyAdded = absenceSheduleList.
                        FirstOrDefault(abs => abs.EmpId == item.EmpId &&
                    ((item.StartDate - abs.StartDate).Days >= 0 &&
                    (abs.EndDate - item.StartDate).Days >= 0));
                    if (_alreadyAdded != null && _alreadyAdded.Type == item.Type)
                    {
                        continue;
                    }
                    if (_alreadyAdded != null)
                    {
                        if (_alreadyAdded.StartDate == _alreadyAdded.EndDate)
                        {
                            var _contain = absenceSheduleList.FirstOrDefault(i => i.EmpId == item.EmpId && i.Type == item.Type && i.EndDate == item.StartDate.Subtract(new TimeSpan(1, 0, 0, 0)));
                            if (_contain != null)
                            {
                                absenceSheduleList.Remove(_alreadyAdded);
                                _contain.EndDate = item.StartDate;
                            }
                            else
                            {
                                _contain = absenceSheduleList.FirstOrDefault(i => i.EmpId == item.EmpId && i.Type == item.Type && i.StartDate == item.StartDate.Add(new TimeSpan(1, 0, 0, 0)));
                                if (_contain != null)
                                {
                                    absenceSheduleList.Remove(_alreadyAdded);
                                    _contain.StartDate = item.StartDate;
                                }
                                else
                                {

                                    _alreadyAdded.Type = item.Type;
                                }
                            }
                        }
                        else if (_alreadyAdded.StartDate == item.StartDate)
                        {
                            var _contain = absenceSheduleList.FirstOrDefault(i => i.EmpId == item.EmpId && i.Type == item.Type && i.EndDate == item.StartDate.Subtract(new TimeSpan(1, 0, 0, 0)));
                            if (_contain != null)
                            {
                                _contain.EndDate = item.StartDate;
                            }
                            else
                            {
                                absenceSheduleList.Add(new AbsenceSchedule()
                                {
                                    EmpId = item.EmpId,
                                    StartDate = item.StartDate,
                                    EndDate = item.EndDate,
                                    Type = item.Type
                                });
                            }
                            _alreadyAdded.StartDate = item.StartDate.Add(new TimeSpan(1, 0, 0, 0));
                        }
                        else if (_alreadyAdded.EndDate == item.StartDate)
                        {
                            var _contain = absenceSheduleList.FirstOrDefault(i => i.EmpId == item.EmpId && i.Type == item.Type && i.StartDate == item.StartDate.Add(new TimeSpan(1, 0, 0, 0)));
                            if (_contain != null)
                            {
                                _contain.StartDate = item.StartDate;
                            }
                            else
                            {
                                absenceSheduleList.Add(new AbsenceSchedule()
                                {
                                    EmpId = item.EmpId,
                                    StartDate = item.StartDate,
                                    EndDate = item.EndDate,
                                    Type = item.Type
                                });
                            }
                            _alreadyAdded.EndDate = item.StartDate.Subtract(new TimeSpan(1, 0, 0, 0));
                        }
                        else
                        {
                            absenceSheduleList.Add(new AbsenceSchedule()
                            {
                                EmpId = _alreadyAdded.EmpId,
                                StartDate = item.StartDate.Add(new TimeSpan(1, 0, 0, 0)),
                                EndDate = _alreadyAdded.EndDate,
                                Type = _alreadyAdded.Type
                            });
                            absenceSheduleList.Add(new AbsenceSchedule()
                            {
                                EmpId = item.EmpId,
                                StartDate = item.StartDate,
                                EndDate = item.EndDate,
                                Type = item.Type
                            });
                            _alreadyAdded.EndDate = item.StartDate.Subtract(new TimeSpan(1, 0, 0, 0));
                        }
                    }
                    else
                    {
                        var _contain = absenceSheduleList.FirstOrDefault(i => i.EmpId == item.EmpId && i.Type == item.Type && i.EndDate == item.StartDate.Subtract(new TimeSpan(1, 0, 0, 0)));
                        if (_contain != null)
                        {
                            _contain.EndDate = item.StartDate;
                            var _containNext = absenceSheduleList.FirstOrDefault(i => i.EmpId == item.EmpId && i.Type == item.Type && i.StartDate == item.StartDate.Add(new TimeSpan(1, 0, 0, 0)));
                            if (_containNext != null)
                            {
                                _contain.EndDate = _containNext.EndDate;
                                absenceSheduleList.Remove(_containNext);
                            }
                        }
                        else
                        {
                            _contain = absenceSheduleList.FirstOrDefault(i => i.EmpId == item.EmpId && i.Type == item.Type && i.StartDate == item.StartDate.Add(new TimeSpan(1, 0, 0, 0)));
                            if (_contain != null)
                            {
                                _contain.StartDate = item.StartDate;
                            }
                            else
                            {

                                absenceSheduleList.Add(new AbsenceSchedule()
                                {
                                    EmpId = item.EmpId,
                                    StartDate = item.StartDate,
                                    EndDate = item.EndDate,
                                    Type = item.Type
                                });
                            }
                        }
                    }
                }

                //Remove Update
                foreach (var item in _removeFromList.Where(i => i.EmpId == absence.Id))
                {
                    AbsenceSchedule _alreadyAdded = absenceSheduleList.FirstOrDefault(abs => abs.EmpId == item.EmpId && item.Type != abs.Type && ((item.StartDate - abs.StartDate).Days >= 0 && (abs.EndDate - item.StartDate).Days >= 0));
                    if (_alreadyAdded != null)
                    {
                        if ((item.StartDate - _alreadyAdded.StartDate).Days >= 1 && (_alreadyAdded.EndDate - item.StartDate).Days >= 1)
                        {
                            AbsenceSchedule _addNew = new AbsenceSchedule()
                            {
                                EmpId = _alreadyAdded.EmpId,
                                Type = _alreadyAdded.Type,
                                StartDate = item.StartDate.Add(new TimeSpan(1, 0, 0, 0)),
                                EndDate = _alreadyAdded.EndDate

                            };
                            _alreadyAdded.EndDate = item.StartDate.Subtract(new TimeSpan(1, 0, 0, 0));
                            absenceSheduleList.Add(_addNew);
                        }
                        else if ((item.StartDate - _alreadyAdded.StartDate).Days >= 1)
                        {
                            _alreadyAdded.EndDate = item.EndDate.Subtract(new TimeSpan(1, 0, 0, 0));
                        }
                        else if ((_alreadyAdded.EndDate - item.EndDate).Days >= 1)
                        {
                            _alreadyAdded.StartDate = item.StartDate.Add(new TimeSpan(1, 0, 0, 0));
                        }
                        else
                        {
                            absenceSheduleList.Remove(_alreadyAdded);
                        }

                    }
                }
            }
        }
        private void SetAlignmentforAllRows()
        {
            fpSpread.Sheets[0].Rows[-1, -1].VerticalAlignment = CellVerticalAlignment.Center;
            fpSpread.Sheets[0].Rows[-1, -1].HorizontalAlignment = CellHorizontalAlignment.Center;
        }
        private void SetRowHeight()
        {
            int _rowCnt = fpSpread.Sheets[0].Rows.Count - 1;
            fpSpread.Sheets[0].Rows[7, _rowCnt].Height = 32;
        }
        private void CreateCondionalRules()
        {

            //Condition rule for S
            sRule = new UnaryComparisonConditionalFormattingRule(UnaryComparisonOperator.EqualTo, "S", false);
            sRule.Operator = UnaryComparisonOperator.EqualTo;
            sRule.Value = "S";
            sRule.BackColor = Color.FromArgb(154, 162, 124);
            sRule.FontStyle = new SpreadFontStyle(RegularBoldItalicFontStyle.Bold);

            //condition rule for V
            vRule = new UnaryComparisonConditionalFormattingRule(UnaryComparisonOperator.EqualTo, "V", false);
            vRule.Operator = UnaryComparisonOperator.EqualTo;
            vRule.Value = "V";
            vRule.BackColor = Color.FromArgb(174, 96, 82);
            vRule.FontStyle = new SpreadFontStyle(RegularBoldItalicFontStyle.Bold);

            //condition rule for P
            pRule = new UnaryComparisonConditionalFormattingRule(UnaryComparisonOperator.EqualTo, "P", false);
            pRule.Operator = UnaryComparisonOperator.EqualTo;
            pRule.Value = "P";
            pRule.BackColor = Color.FromArgb(185, 159, 141);
            pRule.FontStyle = new SpreadFontStyle(RegularBoldItalicFontStyle.Bold);

            //condition rule for C1
            c1Rule = new UnaryComparisonConditionalFormattingRule(UnaryComparisonOperator.EqualTo, "C1", false);
            c1Rule.Operator = UnaryComparisonOperator.EqualTo;
            c1Rule.Value = "C1";
            c1Rule.BackColor = Color.FromArgb(197, 173, 99);
            c1Rule.FontStyle = new SpreadFontStyle(RegularBoldItalicFontStyle.Bold);

            //condition rule for C2
            c2Rule = new UnaryComparisonConditionalFormattingRule(UnaryComparisonOperator.EqualTo, "C2", false);
            c2Rule.Operator = UnaryComparisonOperator.EqualTo;
            c2Rule.Value = "C2";
            c2Rule.BackColor = Color.FromArgb(146, 149, 175);
            c2Rule.FontStyle = new SpreadFontStyle(RegularBoldItalicFontStyle.Bold);

        }
        private void SetConditionalFormatting()
        {
            if (cf != null)
            {
                cf.Clear();
                cf = null;
                fpSpread.ActiveSheet.Models.ConditionalFormatting.Clear();
            }
            cf = new ConditionalFormatting(new CellRange(0, 0, fpSpread.ActiveSheet.Rows.Count, fpSpread.ActiveSheet.Columns.Count), new IConditionalFormattingRule[] { sRule, vRule, pRule, c1Rule, c2Rule });
            fpSpread.ActiveSheet.Models.ConditionalFormatting.Add(cf);

        }
        private void UpdateFooter()
        {
            int _rowCnt = fpSpread.Sheets[0].Rows.Count;
            int _colCnt = fpSpread.Sheets[0].Columns.Count;
            fpSpread.Sheets[0].Rows.Count = _rowCnt + 1;

            //Update colors
            fpSpread.Sheets[0].Rows[_rowCnt].BackColor = Color.FromArgb(187, 189, 178);
            fpSpread.Sheets[0].Cells[7, 33, _rowCnt - 1, 34].BackColor = Color.FromArgb(198, 198, 198);
            fpSpread.Sheets[0].FrozenTrailingRowCount = 1;
            //Update border
            fpSpread.Sheets[0].Cells[5, 33, _rowCnt, 34].Border = new LineBorder(Color.Transparent, 1);

            //Set Text for some fields
            fpSpread.Sheets[0].Cells[6, 1].Text = "Employee Name";
            fpSpread.Sheets[0].Cells[6, _colCnt - 2].Text = "Total Days";
            fpSpread.Sheets[0].Cells[_rowCnt, 1].Value = "Total Days:";

            //Set Readonly first column data cells          
            fpSpread.Sheets[0].Rows[_rowCnt].Locked = true;
            for (int i = 7; i < _rowCnt; i++)
            {
                fpSpread.Sheets[0].Cells[i, 1, i, 1].Locked = true;
            }


            //Span cells
            for (int i = 7; i <= _rowCnt; i++)
            {
                fpSpread.Sheets[0].AddSpanCell(i, _colCnt - 2, 1, 2);
            }
            //Set alignment
            fpSpread.Sheets[0].Cells[7, 33, _rowCnt, _colCnt - 1].HorizontalAlignment = CellHorizontalAlignment.Left;
            fpSpread.Sheets[0].Cells[7, 33, _rowCnt, _colCnt - 1].CellPadding.Left = 5;





        }
        private void UpdateFormulas(int row, int col)
        {
            int _rowCnt = fpSpread.Sheets[0].Rows.Count;
            int _colCnt = fpSpread.Sheets[0].Columns.Count - 2;

            for (int _row = 0; _row < mainTable.Rows.Count - 1; _row++)
            {
                fpSpread.Sheets[0].Cells[_row + 7, _colCnt].Formula = "COUNTIF(C" + (_row + 8) + ":AG" + (_row + 8) + ",\"S\") + COUNTIF(C" + (_row + 8) + ":AG" + (_row + 8) + ",\"V\") + COUNTIF(C" + (_row + 8) + ":AG" + (_row + 8) + ",\"P\") + COUNTIF(C" + (_row + 8) + ":AG" + (_row + 8) + ",\"C1\") + COUNTIF(C" + (_row + 8) + ":AG" + (_row + 8) + ",\"C2\")";
            }
            for (int _col = 2; _col < mainTable.Columns.Count; _col++)
            {
                fpSpread.Sheets[0].Cells[_rowCnt - 1, _col].Formula = "COUNTIF(" + fpSpread.Sheets[0].Columns[_col].Label + "8:" + fpSpread.Sheets[0].Columns[_col].Label + "" + (_rowCnt - 1) + ",\"S\") + COUNTIF(" + fpSpread.Sheets[0].Columns[_col].Label + "8:" + fpSpread.Sheets[0].Columns[_col].Label + "" + (_rowCnt - 1) + ",\"V\")+ COUNTIF(" + fpSpread.Sheets[0].Columns[_col].Label + "8:" + fpSpread.Sheets[0].Columns[_col].Label + "" + (_rowCnt - 1) + ",\"P\")+ COUNTIF(" + fpSpread.Sheets[0].Columns[_col].Label + "8:" + fpSpread.Sheets[0].Columns[_col].Label + "" + (_rowCnt - 1) + ",\"C1\")+ COUNTIF(" + fpSpread.Sheets[0].Columns[_col].Label + "8:" + fpSpread.Sheets[0].Columns[_col].Label + "" + (_rowCnt - 1) + ",\"C2\")";
            }
            fpSpread.Sheets[0].Cells[_rowCnt - 1, _colCnt].Formula = "SUM(AH7:AH" + (_rowCnt - 2) + ")";
        }
        private void updateCombobox(int row, int col)
        {
            fpSpread.Sheets[0].Cells[row + 1, col].Text = month[selectedMonth - 1].ToString();
            fpSpread.Sheets[0].Cells[row + 1, col + 1].Text = selectedYr.ToString();

        }
        private void AddComboBox(int row, int col)
        {

            //Add combobox for month
            fpSpread.Sheets[0].Cells[row, col].Text = "Select Month:";
            if (cmbocellMonth == null)
            {
                cmbocellMonth = new ComboBoxCellType();
                month = new List<string>();
                month.AddRange(new String[] { "January", "February", "March", "April", "May", "June", "July", "August", "September", "October", "November", "December" });
                cmbocellMonth.Items = month.ToArray();
                cmbocellMonth.Editable = false;
                cmbocellMonth.MaxDrop = 8;

            }
            fpSpread.ButtonDrawMode = ButtonDrawModes.AlwaysPrimaryButton;
            fpSpread.Sheets[0].Cells[row + 1, col].CellType = cmbocellMonth;
            fpSpread.Sheets[0].Cells[row + 1, col].Text = month[selectedMonth - 1].ToString();

            //Add a combobox for year
            fpSpread.Sheets[0].Cells[row, col + 1].Text = "Select Year:";
            if (cmbocellYear == null)
            {
                cmbocellYear = new ComboBoxCellType();
                year = new List<string>();
                for (int yr = DateTime.Now.Year - 1; yr <= DateTime.Now.Year + 1; yr++)
                {
                    year.Add(yr + "");
                }
                cmbocellYear.Items = year.ToArray();
                cmbocellYear.Editable = false;
                cmbocellYear.MaxDrop = 8;
            }

            fpSpread.Sheets[0].Cells[row + 1, col + 1].CellType = cmbocellYear;
            fpSpread.Sheets[0].Cells[row + 1, col + 1].Text = selectedYr.ToString();

        }
        private void UpdateDataOnSpread()
        {
            fpSpread.SuspendLayout();
            int _rowCnt = fpSpread.Sheets[0].Rows.Count;
            if (_rowCnt > 7)
            {
                //Remove all data rows
                fpSpread.Sheets[0].Rows.Remove(7, _rowCnt - 7);
            }
            if (mainTable.Rows.Count > 0)
            {
                fpSpread.Sheets[0].Rows.Add(7, mainTable.Rows.Count);

                SpreadDataBindingAdapter data = new SpreadDataBindingAdapter();
                // Assign the datasource to a data table
                data.DataSource = mainTable;
                data.Spread = fpSpread;
                data.SheetName = "Sheet1";
                data.MapperInfo = new MapperInfo(7, 0, mainTable.Rows.Count - 1, 33);
                data.FillSpreadDataByDataSource();
                UpdateFooter();
                UpdateFormulas(3, 33);

            }
            else
            {
                fpSpread.Sheets[0].Rows.Add(7, 1);
                fpSpread.Sheets[0].Rows[7].BackColor = Color.AliceBlue;
                fpSpread.Sheets[0].Cells[7, 1].Text = "No record found.";
                fpSpread.Sheets[0].AddSpanCell(7, 1, 1, 33);
            }
            SetConditionalFormatting();
            SetRowHeight();
            SetAlignmentforAllRows();
            updateCombobox(3, 33);
            fpSpread.ResumeLayout();
        }
        void UpdateDateHeaderAndDateTable(DateTime datetime, List<Spread.Common.DataStore.Models.Employee> empList, List<Spread.Common.DataStore.Models.AbsenceSchedule> absenceSheduleList)
        {
            mainTable = new DataTable();
            mainTable.Columns.Add("EmpId");
            mainTable.Columns.Add("Name");
            int _noOfDays = DateTime.DaysInMonth(datetime.Year, datetime.Month);
            fpSpread.Sheets[0].Cells[5, 1, fpSpread.Sheets[0].Rows.Count - 1, fpSpread.Sheets[0].Columns.Count - 3].Text = "";
            for (int day = 1; day <= _noOfDays; day++)
            {
                if (!mainTable.Columns.Contains(day.ToString()))
                {
                    fpSpread.Sheets[0].Cells[5, day + 1].Text = new DateTime(datetime.Year, datetime.Month, day).ToString("ddd");
                    fpSpread.Sheets[0].Cells[6, day + 1].Text = day.ToString();
                    mainTable.Columns.Add("D" + day.ToString());
                }
            }

            List<DayOfMonth> daysOfMonth = new List<DayOfMonth>();
            foreach (Employee emp in empList.Where(e => e.DOJ <= new DateTime(datetime.Year, datetime.Month, 1) && (e.DOR == null || e.DOR >= new DateTime(datetime.Year, datetime.Month, 1))))
            {
                for (int i = 1; i <= _noOfDays; i++)
                {
                    daysOfMonth.Add(new DayOfMonth()
                    {
                        EmpId = emp.Id,
                        Name = emp.Name,
                        Day = i,
                        Month = datetime.Month,
                        Year = datetime.Year,
                        Type = ""
                    });
                }
            }
            var q = (
            from a in daysOfMonth
            from b in absenceSheduleList.Where(t => new DateTime(a.Year, a.Month, a.Day) >= t.StartDate && new DateTime(a.Year, a.Month, a.Day) <= t.EndDate && t.EmpId == a.EmpId).DefaultIfEmpty()
            select new DayOfMonth()
            {
                EmpId = a.EmpId,
                Name = a.Name,
                Day = a.Day,
                Month = a.Month,
                Year = a.Year,
                Type = (b == null) ? "" : b.Type
            }
           ).ToList();

            var grps1 = q.GroupBy(t => t.Day).ToList();
            List<dynamic> datas = new List<dynamic>();

            grps1.ToList().ForEach(t =>
            {
                var items = t.ToList();
                for (int i = 0; i < items.Count; i++)
                {
                    if (datas.Count <= i)
                    {
                        dynamic data1 = new ExpandoObject();
                        datas.Add(data1);
                        IDictionary<string, object> dataDictionary1 = (IDictionary<string, object>)datas[i];
                        dataDictionary1.Add("EmpId", items[i].EmpId);
                        IDictionary<string, object> dataDictionary2 = (IDictionary<string, object>)datas[i];
                        dataDictionary2.Add("Name", items[i].Name);
                    }
                    IDictionary<string, object> dataDictionary = (IDictionary<string, object>)datas[i];
                    dataDictionary.Add("D" + items[i].Day.ToString(), items[i].Type);
                }
            });

            foreach (var data in datas)
            {
                ExpandoObject obj = data as ExpandoObject;
                DataRow _row = mainTable.NewRow();

                foreach (var item in obj.ToList())
                {
                    _row[item.Key] = item.Value;
                }
                mainTable.Rows.Add(_row);
            }
        }
        #endregion
    }
}


//
//AbsenceScheduleControl.Designer.cs
//


namespace GrapeCity.EmployeeAbsenceSchedule
{
    partial class AbsenceScheduleControl
    {
        /// <summary> 
        /// Required designer variable.
        /// </summary>
        private System.ComponentModel.IContainer components = null;

        /// <summary> 
        /// Clean up any resources being used.
        /// </summary>
        /// <param name="disposing">true if managed resources should be disposed; otherwise, false.</param>
        protected override void Dispose(bool disposing)
        {
            if (disposing && (components != null))
            {
                components.Dispose();
            }
            base.Dispose(disposing);
        }

        #region Component Designer generated code

        /// <summary> 
        /// Required method for Designer support - do not modify 
        /// the contents of this method with the code editor.
        /// </summary>
        private void InitializeComponent()
        {
            components = new System.ComponentModel.Container();
            this.AutoScaleMode = System.Windows.Forms.AutoScaleMode.Font;
        }

        #endregion
    }
}
